package com.sqbang.dbcompare.biz;

import com.sqbang.dbcompare.constant.CommonConst;
import com.sqbang.dbcompare.constant.enums.FieldTypeEnum;
import com.sqbang.dbcompare.pojo.bo.FieldBo;
import com.sqbang.dbcompare.pojo.model.BizException;
import com.sqbang.dbcompare.util.Tools;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.stereotype.Component;
import org.springframework.util.CollectionUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 数据库数据差异sql 业务类
 */
@Component
public class DifferentDataBiz {

    @Autowired
    DatabaseBiz databaseBiz;

    /**
     * 分割符
     */
    private static final String SPLIT_SYMBOL = "※※※";

    public List<String> getDifferentDataSql(Integer changeKey, Integer targetKey, String tableName, String whereNameStr) {

        Connection changeDbConn = Tools.getConnection(changeKey);
        Connection targetDbConn = Tools.getConnection(targetKey);

        try {
            // 步骤1：获取表结构，判断字段的下标数字
            LinkedHashMap<String, FieldBo> changeDbFieldLinkedHashMap = databaseBiz.obtainTableBody(databaseBiz.commonQuery(changeDbConn, CommonConst.MySql.QRY_DESCRIBE + tableName));
            LinkedHashMap<String, FieldBo> targetDbFieldLinkedHashMap = databaseBiz.obtainTableBody(databaseBiz.commonQuery(changeDbConn, CommonConst.MySql.QRY_DESCRIBE + tableName));

            // 步骤2：判断两个表的结构是否一样，不一样则无法比较
            if (changeDbFieldLinkedHashMap.size() != targetDbFieldLinkedHashMap.size()) {
                throw new BizException(HttpStatus.BAD_REQUEST, "无法比较，两张表的字段数量不一致");
            }
            changeDbFieldLinkedHashMap.forEach((columnName, fieldBo) -> {
                FieldBo tFieldBo = targetDbFieldLinkedHashMap.get(columnName);
                if (tFieldBo == null) {
                    throw new BizException(HttpStatus.BAD_REQUEST, "无法比较，两张表的字段名称不一致");
                }
                if (!fieldBo.getFieldTypeEnum().equals(tFieldBo.getFieldTypeEnum())) {
                    throw new BizException(HttpStatus.BAD_REQUEST, "无法比较，两张表的字段数据类型不一致");
                }
            });


            Set<String> whereNameSet = new HashSet<>();
            String[] split = whereNameStr.split(",");
            List<String> whereNameList = new ArrayList<>(split.length);
            for (String fieldName : split) {
                whereNameSet.add(fieldName);
            }


            // 找出每列的数据类型，key是列的下标， value是数据类型
            Map<String, FieldTypeEnum> fieldTypeMap = new LinkedHashMap<>();
            List<String> fieldNameList = new ArrayList<>(changeDbFieldLinkedHashMap.keySet());
            for (int i = 0; i < fieldNameList.size(); i++) {
                String dbFieldName = fieldNameList.get(i);
                FieldBo fieldBo = changeDbFieldLinkedHashMap.get(dbFieldName);
                fieldTypeMap.put(dbFieldName, fieldBo.getFieldTypeEnum());
                if (whereNameSet.contains(dbFieldName)) {
                    whereNameList.add(dbFieldName);
                }
            }

            // 步骤2：查询两个库的数据
            final String selectSql = "select * from " + tableName;
            ResultSet changeDbResultSet = databaseBiz.commonQuery(changeDbConn, selectSql);
            ResultSet targetDbResultSet = databaseBiz.commonQuery(targetDbConn, selectSql);

            // 将结果集按唯一字段转为map，key为唯一字段的下标字符串，value是数据记录结果集
            Map<String, Map<String, Object>> changeDbDataMap = this.getTableData(changeDbResultSet, fieldNameList, fieldTypeMap, whereNameSet);
            Map<String, Map<String, Object>> targetDbDataMap = this.getTableData(targetDbResultSet, fieldNameList, fieldTypeMap, whereNameSet);

            // 步骤3：根据字段名对比出新增、修改、删除的数据记录，并形成sql
            return this.getSqlFromDataMap(tableName, changeDbDataMap, targetDbDataMap, whereNameList, fieldTypeMap);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    /**
     * 从 resultSet里面获取数据
     * @param rs
     * @param fieldNameList 数据库字段名称列表
     * @return 最外面的map的key是唯一字段，里面的map的key是列的下标，value是列的数据
     */
    private Map<String, Map<String, Object>> getTableData(ResultSet rs, List<String> fieldNameList,
                                                           Map<String, FieldTypeEnum> fieldTypeMap,
                                                           Set<String> whereNameSet) throws SQLException {
        Map<String, Map<String, Object>> resultMap = new LinkedHashMap<>();
        while (rs.next()) {
            String uniqueData = "";
            Map<String, Object> data = new LinkedHashMap<>();
            for (int i = 1; i <= fieldNameList.size(); i++) {
                int idx = i - 1;
                String fieldName = fieldNameList.get(idx);
                FieldTypeEnum fieldTypeEnum = fieldTypeMap.get(fieldName);
                try {
                    if (FieldTypeEnum.BIGINT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getLong(i));
                    } else if (FieldTypeEnum.CHAR.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.DATE.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getDate(i));
                    } else if (FieldTypeEnum.DATETIME.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getTimestamp(i, Calendar.getInstance(Locale.CHINA)));
                    } else if (FieldTypeEnum.DECIMAL.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getBigDecimal(i));
                    } else if (FieldTypeEnum.DOUBLE.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getDouble(i));
                    } else if (FieldTypeEnum.FLOAT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getFloat(i));
                    } else if (FieldTypeEnum.INT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getInt(i));
                    } else if (FieldTypeEnum.INTEGER.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getInt(i));
                    } else if (FieldTypeEnum.JSON.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.LINESTRING.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.LONGTEXT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.MULTILINESTRING.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.NUMERIC.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getBigDecimal(i));
                    } else if (FieldTypeEnum.SMALLINT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getInt(i));
                    } else if (FieldTypeEnum.TEXT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.TIME.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getTime(i));
                    } else if (FieldTypeEnum.TIMESTAMP.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getTimestamp(i, Calendar.getInstance(Locale.CHINA)));
                    } else if (FieldTypeEnum.TINYINT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getInt(i));
                    } else if (FieldTypeEnum.TINYTEXT.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    } else if (FieldTypeEnum.VARCHAR.equals(fieldTypeEnum)) {
                        data.put(fieldName, rs.getString(i));
                    }
                    if (whereNameSet.contains(fieldName)) {
                        uniqueData = uniqueData + ("".equals(uniqueData) ? "" : SPLIT_SYMBOL) + data.get(fieldName).toString();
                    }
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }
            resultMap.put(uniqueData, data);
        }
        return resultMap;
    }

    /**
     * 从数据map里面对比得到sql
     * @return
     */
    private List<String> getSqlFromDataMap(String tableName, Map<String, Map<String, Object>> changeDbDataMap, Map<String, Map<String, Object>> targetDbDataMap,
                                           List<String> whereNameList, Map<String, FieldTypeEnum> fieldTypeMap) {
        List<String> resultList = new ArrayList<>();

        List<String> deleteSqlList = new ArrayList<>();
        List<String> updateSqlList = new ArrayList<>();
        List<String> insertSqlList = new ArrayList<>();

        //（1）找删除了的数据，即 targetDbDataMap 不在 changeDbDataMap 里面的数据
        //（2）找修改了的数据，即 changeDbDataMap 和 targetDbDataMap 的key相同，但是每项的数据有不同
        targetDbDataMap.forEach((uniqueData, dataMap) -> {
            String whereSql = this.getWhereSql(uniqueData, whereNameList, fieldTypeMap);
            Map<String, Object> changeDataMap = changeDbDataMap.get(uniqueData);
            if (CollectionUtils.isEmpty(changeDataMap)) {
                deleteSqlList.add(String.format("delete from %s where %s;", tableName, whereSql));
            } else {
                List<String> setSqlList = new ArrayList<>();
                dataMap.forEach((fieldName, data) -> {
                    Object changeData = changeDataMap.get(fieldName);
                    if (!data.equals(changeData)) {
                        boolean isStringData = this.isStringData(fieldName, fieldTypeMap);
                        setSqlList.add(fieldName + " = " + (isStringData ? "'" : "") + changeData + (isStringData ? "'" : ""));
                    }
                });
                if (!CollectionUtils.isEmpty(setSqlList)) {
                    updateSqlList.add(String.format("update %s set %s where %s;", tableName, setSqlList.stream().collect(Collectors.joining(",")), whereSql));
                }
            }
        });
        //（3）找新增了的数据，即 changeDbDataMap 不在 targetDbDataMap 里面的数据
        changeDbDataMap.forEach((uniqueData, dataMap) -> {
            if (!targetDbDataMap.containsKey(uniqueData)) {
                List<String> fieldList = new ArrayList<>();
                List<String> dataList = new ArrayList<>();
                dataMap.forEach((fieldName, data) -> {
                    boolean isStringData = this.isStringData(fieldName, fieldTypeMap);
                    fieldList.add(fieldName);
                    dataList.add((isStringData ? "'" : "") + data + (isStringData ? "'" : ""));
                });
                insertSqlList.add(String.format("insert into %s (%s) values (%s);", tableName, fieldList.stream().collect(Collectors.joining(",")), dataList.stream().collect(Collectors.joining(","))));
            }
        });
        if (!CollectionUtils.isEmpty(deleteSqlList)) {
            resultList.addAll(deleteSqlList);
        }
        if (!CollectionUtils.isEmpty(updateSqlList)) {
            resultList.addAll(updateSqlList);
        }
        if (!CollectionUtils.isEmpty(insertSqlList)) {
            resultList.addAll(insertSqlList);
        }
        return resultList;
    }

    private String getWhereSql(String uniqueData, List<String> whereNameList, Map<String, FieldTypeEnum> fieldTypeMap) {
        String[] dataSplit = uniqueData.split(SPLIT_SYMBOL);
        String whereSql = "";
        for (int i = 0; i < dataSplit.length; i++) {
            if (i != 0) {
                whereSql += " and ";
            }
            String fieldName = whereNameList.get(i);
            boolean isStringData = this.isStringData(fieldName, fieldTypeMap);
            whereSql += fieldName;
            whereSql += " = " + (isStringData ? "'" : "") + dataSplit[i] + (isStringData ? "'" : "");
        }
        return whereSql;
    }

    /**
     * 数据类型是否是 String
     */
    private boolean isStringData(String fieldName, Map<String, FieldTypeEnum> fieldTypeMap) {
        FieldTypeEnum fieldTypeEnum = fieldTypeMap.get(fieldName);
        if (FieldTypeEnum.BIGINT.equals(fieldTypeEnum)
                || FieldTypeEnum.DECIMAL.equals(fieldTypeEnum)
                || FieldTypeEnum.DOUBLE.equals(fieldTypeEnum)
                || FieldTypeEnum.FLOAT.equals(fieldTypeEnum)
                || FieldTypeEnum.INT.equals(fieldTypeEnum)
                || FieldTypeEnum.INTEGER.equals(fieldTypeEnum)
                || FieldTypeEnum.NUMERIC.equals(fieldTypeEnum)
                || FieldTypeEnum.SMALLINT.equals(fieldTypeEnum)
                || FieldTypeEnum.TIMESTAMP.equals(fieldTypeEnum)
                || FieldTypeEnum.TINYINT.equals(fieldTypeEnum)
        ) {
            return false;
        } else {
            return true;
        }
    }
}
